CREATE DATABASE QUANLYNHAHANG
USE QUANLYNHAHANG

CREATE TABLE TaiKhoan
(
	maTK varchar(5) primary key,
	tenDN varchar(20) not null unique,
	matKhau varchar(40) not null,
	maLoaiTK varchar(5),
	email varchar(200),
	dienThoai varchar(20)
)

CREATE TABLE LoaiTaiKhoan
(
	maLoaiTK varchar(5) primary key,
	tenLoai nvarchar(30)
)

CREATE TABLE NhanVien
(
	maNV varchar(5) primary key,
	tenNV nvarchar(100) not null,
	maTK varchar(5),
	diaChi nvarchar(200),
	email varchar(200),
	dienThoai varchar(20),
	ngayVaoLam datetime,
	luongCB float,
	hsLuong float
)

CREATE TABLE MonAn
(
	maMA varchar(5) primary key,
	tenMA nvarchar(100),
	donGia float,
	mucGiamGia float
)

CREATE TABLE DatMon
(
	maDM varchar(5) primary key,
	nguoiLap varchar(5),
	ngayDat datetime,
	thanhTien float,
	tenKH nvarchar(100),
	dienThoaiKH varchar(20)
)

CREATE TABLE CT_DatMon
(
	maCTDM varchar(5) not null,
	maDM varchar(5) not null,
	maMA varchar(5) not null,
	soLuong int,
	donGia float,
	constraint pk_ctdatmon primary key(maCTDM, maDM)
)

CREATE TABLE HoaDon
(
	maHD varchar(5) primary key,
	maDM varchar(5),
	ngayLap datetime,
	nguoiLap varchar(5),
	ThanhTien Float
)

CREATE TABLE CT_HoaDon
(
	maHD varchar(5) not null,
	maMA varchar(5) not null,
	soLuong int,
	donGia float,
	constraint pk_cthoadon primary key(maHD, maMA)
)

CREATE TABLE Ban
(
	maBan varchar(5) primary key,
	tinhTrang tinyint
)

CREATE TABLE DatBan
(
	maDB varchar(5) primary key,
	tenKH nvarchar(100),
	dienThoaiKH varchar(20),
	soLuong int,
	thanhTien float,
	ngayLap datetime,
	nguoiLap varchar(5)
)

CREATE TABLE CT_DatBan
(
	maDB varchar(5) not null,
	maBan varchar(5) not null,
	constraint pk_ctdatban primary key(maDB, maBan)
)

alter table TaiKhoan
add constraint fk_taikhoan_loaitk foreign key(maLoaiTK)
references LoaiTaiKhoan(maLoaiTK)

alter table NhanVien
add constraint fk_nhanvien_taikhoan foreign key(maTK)
references TaiKhoan(maTK)

alter table DatMon
add constraint fk_datmon_nhanvien foreign key(nguoiLap)
references NhanVien(maNV)

alter table CT_DatMon
add constraint fk_ctdatmon_datmon foreign key(maDM)
references DatMon(maDM)

alter table HoaDon
add constraint fk_hoadon_datmon foreign key(maDM)
references DatMon(maDM)

alter table HoaDon
add constraint fk_hoadon_nhanvien foreign key(nguoiLap)
references NhanVien(maNV)

alter table CT_HoaDon
add constraint fk_cthoadon_hoadon foreign key(maHD)
references HoaDon(maHD)

alter table CT_HoaDon
add constraint fk_cthoadon_monan foreign key(maMA)
references MonAn(maMA)

alter table DatBan
add constraint fk_datban_nhanvien foreign key(nguoiLap)
references NhanVien(maNV)

alter table CT_DatBan
add constraint fk_ctdatban_datban foreign key(maDB)
references DatBan(maDB)

alter table CT_DatBan
add constraint fk_ctdatban_ban foreign key(maBan)
references Ban(maBan)

insert into LoaiTaiKhoan values('LTK01',N'Nhân Viên')
insert into LoaiTaiKhoan values('LTK02',N'Quản Lý')

insert into TaiKhoan values('TK001','chanhduc', 'chanhduc', 'LTK02', 'ncduc@mail','0909465552')
insert into TaiKhoan values('TK002','hoainam', 'hoainam', 'LTK01', 'lnhnam@mail','0908364552')
insert into TaiKhoan values('TK003','hoangdung', 'hoangdung', 'LTK01', 'lhdung@mail','0902344552')
insert into TaiKhoan values('TK004','thanhvinh', 'thanhvinh', 'LTK01', 'ntvinh@mail','0978644552')
insert into TaiKhoan values('TK005','xuanquang', 'xuanquang', 'LTK01', 'pxquang@mail','0909940182')

insert into MonAn values('MA001', N'Bào Ngư Xào Xả Ớt' ,2000000,0 )
insert into MonAn values('MA002', N'Vi Cá Hấp Gừng' ,1500000,0 )
insert into MonAn values('MA003', N'Rau Muống Xào Tỏi' ,450000,0 )
insert into MonAn values('MA004', N'Nui Xào Bò' ,125000,0 )
insert into MonAn values('MA005', N'Lẩu Hải Sản' ,100000,0 )

insert into NhanVien values('NV001', N'Ngô Chánh Đức', 'TK001', N'Trần Phú', 'ncduc@mail.com', '0909465552', '03/08/2010', 3500000, 1.2)
insert into NhanVien values('NV002', N'Lê Nguyễn Hoài Nam', 'TK002', N'Trương Định', 'lnhnam@mail.com', '0908364552', '03/03/2010', 2100000, 2.2)
insert into NhanVien values('NV003', N'Lê Hoàng Dũng', 'TK003', N'An Dương Vương', 'lhdung@mail.com', '0902344552', '01/04/2011', 2600000, 1.5)
insert into NhanVien values('NV004', N'Nguyễn Thành Vinh', 'TK004', N'Lê Hồng Phong', 'ntvinh@mail.com', '0978644552', '08/12/2011', 4200000, 2.2)
insert into NhanVien values('NV005', N'Phạm Xuân Quang', 'TK005', N'Tôn Đản', 'pxquang@mail.com', '0909940182', '11/23/2009', 3700000, 1.8)

insert into DatMon values('DM001', 'NV002', '11/29/2013', 405000, N'Nguyễn Hoàng An','0909123456')
insert into DatMon values('DM002', 'NV002', '07/23/2013', 1205000, N'Nguyễn Hoài Đức','0909123456')
insert into DatMon values('DM003', 'NV003', '12/12/2013', 1532000, N'Nguyễn Văn Linh','0977683112')
insert into DatMon values('DM004', 'NV004', '03/06/2013', 225000, N'Nguyễn Thành Nhân','0923894754')

insert into CT_DatMon values('CT001','DM001', 'MA004', 2,100000 )
insert into CT_DatMon values('CT001','DM001', 'MA005', 1,100000 )
insert into CT_DatMon values('CT001','DM002', 'MA002', 1,1500000 )
insert into CT_DatMon values('CT001','DM003', 'MA002', 1,1500000 )
insert into CT_DatMon values('CT001','DM001', 'MA004', 1,125000 )

insert into DatBan values('DB001', 'Nguyễn Hoàng An', '0909123456', 1,405000, '11/12/2012', 'NV002')
insert into DatBan values('DB002', 'Nguyễn Văn Linh', '0977683112', 1,1532000, '12/12/2013', 'NV003')
insert into DatBan values('DB003', 'guyễn Thành Nhân', '0923894754', 1,225000, '03/06/2013', 'NV005')

insert into CT_DatBan values ('DB001', '001')
insert into CT_DatBan values ('DB002', '002')
insert into CT_DatBan values ('DB003', '003')

insert into Ban values ('001', 1)
insert into Ban values ('002', 1)
insert into Ban values ('003', 1)
insert into Ban values ('004', 0)
insert into Ban values ('005', 0)

--Tính tổng tiền trong Bảng đặt món từ chi tiết đặt món
Create proc ThanhTien
As
Begin
	Declare @c cursor
	Set @c = cursor for (Select maDM, sum(soLuong*donGia) as thanhTien
						From CT_DatMon
						Group by maDM)
	Open @c
	Declare @maDM varchar(20)
	Declare @thanhTien float
	Fetch next from @c into @maDM, @thanhTien
	While @@FETCH_STATUS = 0
	Begin
		Update DatMon Set thanhTien = @thanhTien where maDM = @maDM			
		Fetch next from @c into @maDM, @thanhTien
	End
	Close @c
	Deallocate @c
End
Go

--Lấy đơn giá của món ăn
Create proc LayDonGiaMonAn @maMA varchar(20), @donGia float output
As
Begin
	Select @donGia = donGia
	From MonAn
	Where maMA = @maMA
End
Go

Declare @donGia float
exec LayDonGiaMonAn 'MA01', @donGia output
print CAST(@donGia as varchar(30))



--Cập nhập cột thành tiền ở bảng đặt món bằng 0 nếu chưa thêm chi tiết món ăn hoặc
Create proc ThanhTienBang0
As
Begin
	Declare @d cursor
	Set @d = cursor for (Select d.madm, d.thanhTien From DatMon d where d.madm not in (select c.madm from ct_datmon c group by c.madm))
	Open @d
	Declare @maDM varchar(20)
	Declare @thanhTien float
	Fetch next from @d into @maDM, @thanhTien
	While @@FETCH_STATUS = 0
	Begin
		Update DatMon Set thanhTien = 0 where maDM = @maDM			
		Fetch next from @d into @maDM, @thanhTien
	End
	Close @d
	Deallocate @d				
End
Go

exec ChuaThanhTien
